﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Diagnostics;

namespace Zgq.CommonTools
{
    public class SqlCache
    {

        private static Dictionary<string, List<SqlCacheData>> _sqlCacheData = null;
        /// <summary>
        /// Sql语句缓存集合
        /// </summary>
        public static Dictionary<string, List<SqlCacheData>> SqlCacheDataList
        {
            get
            {
                return _sqlCacheData;
            }
        }

        /// <summary>
        /// 设置视图缓存
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        public static void SetViewCahce(string sysDatabaseName)
        {
            if (_sqlCacheData == null)
                _sqlCacheData = new Dictionary<string, List<Zgq.CommonTools.SqlCacheData>>();

            var list = C5DataBaseHelper.GetModelListStatic<SqlCacheData>(sysDatabaseName, @"select A.ViewCodeVal as SqlStr, REPLACE(B.ENTITY_CODE, ' ', '') as SqlCode, CodeType as CodeType from SYS_ViewCode A left join SYS_ENTITIES B on A.EntityId = B.ENTITY_ID where CodeType = 2");

            if (list != null && list.Count > 0)
                _sqlCacheData.Add(sysDatabaseName, list);
        }

        /// <summary>
        /// 获取视图信息
        /// </summary>
        /// <param name="name"></param>
        /// <param name="sysDatabaseName"></param>
        /// <returns></returns>
        public static SqlCacheData GetSqlByCode(string sysDatabaseName, string name)
        {
            if (_sqlCacheData == null)
                return null;

            var list = SqlCacheDataList[sysDatabaseName];
            if (list == null)
            {
                //重新缓存
                SetViewCahce(sysDatabaseName);
                list = SqlCacheDataList[sysDatabaseName];
            }

            var model = list.FirstOrDefault(x => x.SqlCode.Equals(name, StringComparison.CurrentCultureIgnoreCase));
            return model;
        }


        private static Dictionary<string, Dictionary<string, List<C5SqlModelAttribute>>> _TableColumnInfo = null;
        /// <summary>
        /// 缓存表字段信息
        /// </summary>
        public static Dictionary<string, Dictionary<string, List<C5SqlModelAttribute>>> TableColumnInfo
        {
            get
            {
                return _TableColumnInfo;
            }
            set
            {
                _TableColumnInfo = value;
            }
        }


        /// <summary>
        /// 设置表信息缓存
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        public static void SetTableColumnInfo(string sysDatabaseName)
        {
            if (TableColumnInfo == null)
                _TableColumnInfo = new Dictionary<string, Dictionary<string, List<C5SqlModelAttribute>>>();

            //重复库名称
            if (TableColumnInfo.Count > 0)
            {
                var data = TableColumnInfo.FirstOrDefault(x => x.Key == sysDatabaseName);
                if (data.Key != null)
                    return;
            }

            var list = C5DataBaseHelper.GetModelListStatic<TableColumnInfo>(sysDatabaseName, @" SELECT d.name as  tableName,a.name as FieldName,case COLUMNPROPERTY( a.id,a.name,'IsIdentity') when 1 then 'true' when 0 then 'false' end  as AutoIncrement, 
                        CASE WHEN EXISTS ( SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id 
						AND sc.colid = sik.colid INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK' WHERE sc.id = a.id AND sc.colid = a.colid ) THEN 'true' ELSE 'false' END AS PrimaryKey,
						b.name as DbType,a.length as MaxLength, a.xprec as  Precision,a.xscale as Scale,'false' as IsNotModif,case b.xtype when 189 then 'true' else 'false' end as IsTimeStamp
                        FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join sys.extended_properties f on d.id=f.class and f.minor_id=0
                        where b.name is not null");

            Dictionary<string, List<C5SqlModelAttribute>> dicAttr = new Dictionary<string, List<C5SqlModelAttribute>>();
            var tableList = list.GroupBy(x => x.TableName);
            foreach (var item in tableList)
            {
                dicAttr.Add(item.Key, item.ToList<C5SqlModelAttribute>());
            }

            _TableColumnInfo.Add(sysDatabaseName, dicAttr);
        }

        /// <summary>
        /// 获取表所有列信息
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static List<C5SqlModelAttribute> GetTableColumnInfo(string sysDatabaseName, string tableName)
        {
            var dicTable = TableColumnInfo[sysDatabaseName];
            if (dicTable != null && tableName.Count() > 0)
            {
                return dicTable[tableName];
            }

            return null;
        }

        /// <summary>
        /// 获取表主键
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static List<string> GetKeyList(string sysDatabaseName, string tableName)
        {
            var list = GetTableColumnInfo(sysDatabaseName, tableName);
            if (list == null)
                return null;

            return list.Where(x => x.PrimaryKey).Select(x => x.FieldName).ToList();
        }

        /// <summary>
        /// 获取字段信息
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static C5SqlModelAttribute GetColumnInfo(string sysDatabaseName, string tableName, string fieldName)
        {
            var list = GetTableColumnInfo(sysDatabaseName, tableName);
            if (list == null)
                return null;

            return list.FirstOrDefault(x => x.FieldName == fieldName);
        }

        /// <summary>
        /// 获取数据类型对比
        /// </summary>
        /// <returns></returns>
        public static List<DataType> GetDataTypeList()
        {
            return new List<DataType>()
            {
                new DataType() { CSharpType= "string", SqlType = "nvarchar", C5Type = 1 },
                new DataType() { CSharpType= "DateTime", SqlType = "datetime", C5Type = 2 },
                new DataType() { CSharpType= "int", SqlType = "int", C5Type = 3 },
                new DataType() { CSharpType= "decimal", SqlType = "decimal", C5Type = 4 },
                new DataType() { CSharpType= "bool", SqlType = "bit", C5Type = 5 },
                new DataType() { CSharpType= "long", SqlType = "bigint", C5Type = 6 },
                new DataType() { CSharpType= "byte[]", SqlType = "timestamp", C5Type = 7 },
                new DataType() { CSharpType= "short", SqlType = "smallint", C5Type = 8 },
                new DataType() { CSharpType= "double", SqlType = "float", C5Type = 9 },
                new DataType() { CSharpType= "single", SqlType = "real", C5Type = 10 },
                new DataType() { CSharpType= "Guid", SqlType = "uniqueidentifier", C5Type = 11 },
                new DataType() { CSharpType= "object", SqlType = "variant", C5Type = 12 },
                new DataType() { CSharpType= "DateTime", SqlType = "smalldatetime", C5Type = 13 },
                new DataType() { CSharpType= "byte[]", SqlType = "image", C5Type = 14 },
                new DataType() { CSharpType= "string", SqlType = "varchar", C5Type = 15 },
                new DataType() { CSharpType= "string", SqlType = "char", C5Type = 16 },
                new DataType() { CSharpType= "string", SqlType = "text", C5Type = 17 },
                new DataType() { CSharpType= "string", SqlType = "nchar", C5Type = 18 },
                new DataType() { CSharpType= "string", SqlType = "ntext", C5Type = 19 },
                new DataType() { CSharpType= "DateTime", SqlType = "date", C5Type = 20 },
                new DataType() { CSharpType= "byte[]", SqlType = "varbinary", C5Type = 21 },
            };
        }
    }

    /// <summary>
    /// 缓存Sql语句
    /// </summary>
    public class SqlCacheData
    {
        /// <summary>
        /// 表/视图 代码
        /// </summary>
        public string SqlCode { get; set; }

        /// <summary>
        /// Sql语句
        /// </summary>
        public string SqlStr { get; set; }

        /// <summary>
        /// 语句类型 1:视图名称 2:视图语句 3:表名称
        /// </summary>
        public int CodeType { get; set; }
    }

    /// <summary>
    /// 表信息
    /// </summary>
    public class TableColumnInfo : C5SqlModelAttribute
    {
        /// <summary>
        /// 表名
        /// </summary>
        public string TableName { get; set; }
    }

    /// <summary>
    /// 缓存类型
    /// </summary>
    public enum CacheFlag
    {
        NoCache = 1,

        CacheView = 2,

        CacheColumn = 4,

        CacheAll = 8
    }

    /// <summary>
    /// 数据类型
    /// </summary>
    public class DataType
    {
        /// <summary>
        /// C#类型
        /// </summary>
        public string CSharpType { get; set; }
        /// <summary>
        /// Sql类型
        /// </summary>
        public string SqlType { get; set; }
        /// <summary>
        /// C5对应类型标识
        /// </summary>
        public int C5Type { get; set; }
    }
}
